﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
  public  class IncomeExpensesServiceImpl : BaseServiceImpl, IIncomeExpensesService
    {
        public int delete(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("DELETE FROM joy.IncomeExpenses WHERE ie_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public IList getIncomeExpensesbyid(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT * FROM joy.IncomeExpenses WHERE ie_id=@id");
            query.addParas("@id", id);
            return base.getList(query);
        }
        public IList userGetIncomeExpenses(int pageno, int pagesize, int uid)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            if (pageno == 1)
            {
                sb.Append("SELECT count(ie_id) as total FROM joy.IncomeExpenses where ie_uid=@uid;");
                sb.Append("select top " + pagesize + " *,case ie_state when 0 then '否' when 1 then '是' else '否'  end ie_statetext  from joy.IncomeExpenses where ie_uid=@uid order by ie_time desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,case ie_state when 0 then '否' when 1 then '是' else '否'  end ie_statetext,ROW_NUMBER() OVER (order by ie_time desc) as rno from  joy.IncomeExpenses where ie_uid=@uid ");
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by a.ie_time desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.addParas("@uid", uid);
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
        public IList getIncomeExpenses(string in_s, string in_e, int pageno, int pagesize,string name,string nick)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and ie_time >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and ie_time < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (!String.IsNullOrEmpty(name))
            {
                _sql += " and u_name like '%" + name + "%'";
            }
            if (!String.IsNullOrEmpty(nick))
            {
                _sql += " and u_nick like '%" + nick + "%'";
            }
            if (pageno == 1)
            {
                sb.Append("SELECT count(a.ie_id) as total FROM joy.IncomeExpenses a inner join joy.UserInfo b  on a.ie_uid = b.u_id " + _sql + ";");
                sb.Append("select top " + pagesize + " a.*,b.u_nick,b.u_name,case a.ie_state when 0 then '不需要' when 1 then '需要' else '未知'  end ie_statetext,(select o_workid from cms.Operator where o_id =isnull(ie_lastuser,ie_user)) oper from joy.IncomeExpenses a inner join joy.UserInfo b  on a.ie_uid = b.u_id" + _sql + "  order by ie_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select a.*,b.u_nick,b.u_name,case a.ie_state when 0 then '不需要' when 1 then '需要' else '未知'  end ie_statetext,(select o_workid from cms.Operator where o_id =isnull(ie_lastuser,ie_user)) oper,ROW_NUMBER() OVER (order by a.ie_id desc) as rno from  joy.IncomeExpenses a inner join joy.UserInfo b  on a.ie_uid = b.u_id" + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by a.ie_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
    }
}
